

/*************************************************
Program: pull_mnl.sas
Author: hannah rubinton
Date: 10/09/2018

Summary: output a stata dataset at the foreign affiliate level with information by country and industry 

inputs: 
-2012 affiliate form data set (be11_2012r_fa_faform.sas7bdat)
-2012 crosswalk between us_id and firmid  (be11_crosswalk_2012.sas7bdat)

output: 
1. affilliate level dataset, keys=firmid X foreign_id  
  covariates: emp, sales, assets, industry, country. 



**************************************************/

    
options obs=max;
    
*Raw census files ;
 %include 'yyyy/pdata.sas' ;

*debugging;
libname input 'xxxx/input/' ;

libname hannahcw 'xxxx/input/Hannah/crosswalk' ;

libname transfer 'xxxx/input/transfer20160318/' ;

libname output "xxxx/data/";

%let output=xxxx/data ;    

libname tempdata 'xxxx/data/junk/' ;


%macro pull_mnl(year);


/******************************
*check for duplicates before merging files together;
********************************/
proc freq data=hannahcw.parent_crosswalk_&year;
  tables us_id /noprint out=keylist;
run;
proc print; where count ge 2; run;

proc freq data=transfer.be11_&year.r_fa_faform;
  tables us_id*foreign_id /noprint out=keylist;
run;
proc print; where count ge 2; run;

proc freq data=transfer.be11_&year.r_usr_usrentity;
  tables us_id /noprint out=keylist;
run;
proc print; where count ge 2; run;

proc freq data=transfer.be11_&year.r_usr_usrform;
  tables us_id /noprint out=keylist;
run;
proc print; where count ge 2; run;

proc freq data=transfer.be11_&year.r_fa_faentity;
  tables us_id*foreign_id /noprint out=keylist;
run;
proc print; where count ge 2; run;

/******************************
*. create foreign affiliate level bea dataset;
********************************/

*merge in firmids;
proc sql; 
create table mnl as 
select A.*, B.firmid, C.name as bea_name, D.emp as aff_emp_parent, E.industry_code as aff_ind_parent,
 E.type_ownership as aff_type_ownership,
C.ein_1, C.ein_2, match_flag, E.doc_type as fa_entity_doc_type, C.doc_type as us_entity_doc_type
%if &yr le 2012 %then %do;
  from transfer.be11_&year.r_fa_faform as A 
  left join hannahcw.parent_crosswalk_&year. as B on A.us_id eq B.us_id
  left join transfer.be11_&year.r_usr_usrentity as C on A.us_id eq C.us_id
  left join transfer.be11_&year.r_usr_usrform as D on A.us_id eq D.us_id
  left join transfer.be11_&year.r_fa_faentity as E on A.us_id eq E.us_id and A.foreign_id eq E.foreign_id;
%end;
%else %do;
  from transfer.be11_&year.p_fa_faform as A 
  left join hannahcw.parent_crosswalk_&year. as B on A.us_id eq B.us_id
  left join transfer.be11_&year.p_usr_usrentity as C on A.us_id eq C.us_id
  left join transfer.be11_&year.p_usr_usrform as D on A.us_id eq D.us_id
  left join transfer.be11_&year.p_fa_faentity as E on A.us_id eq E.us_id and A.foreign_id eq E.foreign_id;
%end;

proc freq data=mnl;
  tables us_id*foreign_id /noprint out=keylist;
run;
proc print; where count ge 2; run;


*select variables and create some new indicators of type of affiliate;
proc sql;
create table output.mnl_foreign_affiliates&year as 
select us_id, firmid, bea_name, foreign_id, 
  ein_1 as aff_ein1, ein_2 as aff_ein2, aff_type_ownership,  aff_emp_parent, aff_ind_parent,
  match_flag as aff_match_flag,
  ctry as aff_ctry, 
  ind_1 as aff_ind1, ind_2 as aff_ind2, ind_3 as aff_ind3, ind_4 as aff_ind4, ind_5 as aff_ind5, ind_6 as aff_ind6, ind_7 as aff_ind7, 
  naics_id as aff_naics_id, 
  fa_entity_doc_type, us_entity_doc_type,
  emp as aff_emp, 
  emp_compen as aff_emp_compen, 
  sales as aff_sales,
  sales_oth_fa as aff_sales_oth_fa,
  sales_local_unaff as aff_sales_local_unaff,
  sales_local_fa as aff_sales_local_fa,
  sales_oth_unaff as aff_sales_oth_unaff,
  sales_us_unaff as aff_sales_us_unaff,
  sales_us_rep as aff_sales_us_rep,
  sales_ins as aff_sales_ins,
  value_added as aff_value_added,
  cog as aff_cog,
  goods as aff_goods,
  goods_local_fa as aff_goods_local_fa,
  goods_local_unaff as aff_goods_local_unaff,
  goods_oth_fa as aff_goods_oth_fa,
  goods_oth_unaff as aff_goods_oth_unaff,
  goods_us_rep as aff_goods_us_rep,
  goods_us_unaff as aff_goods_us_unaff,
  svc as aff_svc,
  svc_local_fa as aff_svc_local_fa,
  svc_local_unaff as aff_svc_local_unaff,
  svc_oth_fa as aff_svc_oth_fa,
  svc_oth_unaff as aff_svc_oth_unaff,
  svc_us_rep as aff_svc_us_rep,
  svc_us_unaff as aff_svc_us_unaff,
  im as aff_im,
  im_oth_us as aff_im_oth_us,
  im_us_rep as aff_im_us_rep,
  ex as aff_ex,
  ex_oth_us as aff_ex_oth_us,
  ex_us_rep as aff_ex_us_rep

  %if &year ne 2009 %then %do;
    ,
    mgmt_emp as aff_mgmt_emp, 
    mgmt_compen as aff_mgmt_compen,
    sales_trade as aff_sales_trade,
    assets as aff_assets,
    liab as aff_liab
  %end;	
from mnl ;

data output.mnl_foreign_affiliates&year;
  set output.mnl_foreign_affiliates&year;
   *activity, vertical or horizonatal;
    if aff_sales>0 then do;
      vertical=0;
      if (aff_sales_oth_fa + aff_sales_us_rep + aff_sales_local_fa)/aff_sales > .80 then vertical=1;
    end;
    else do;
      vertical=.;
    end;
run;



proc export data = output.mnl_foreign_affiliates&year
    file = "&output/mnl_foreign_affiliates&year..dta"
    dbms=stata replace;
run;


%mend;




%macro loop_years;
  %do yr= 2007 %to 2007;
    %pull_mnl(&yr.);
  %end;

%mend;

%loop_years;

